/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.just.dusin.ooii.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author Administrator
 */
public class QHSSSJDao {
    private static Logger logger = Logger.getLogger(QHSSSJDao.class.getName());
    private Connection conn = null;
    public int size;
    
    public QHSSSJDao(Connection conn){
        this.conn = conn;
    }
    
    public boolean doCreate(QHSSSJBean qhsssjBean) throws Exception{
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql = "INSERT INTO [QHSSSJ]([MKBH],[SHI],[XIAN],[MKMC],[SBBH],[TSFS],[YXH],[CJSJ],[CC],[XSZL],[SMYC],[JLYC],[WKYC],[TXYC],[FSZT],[YCBS])"
                + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            pstmt = this.conn.prepareStatement(sql);
            
            pstmt.setString(1, qhsssjBean.getMKBH());
            pstmt.setString(2, qhsssjBean.getSHI());
            pstmt.setString(3, qhsssjBean.getXIAN());
            pstmt.setString(4,qhsssjBean.getMKMC());
            pstmt.setString(5, qhsssjBean.getSBBH());
            pstmt.setInt(6, qhsssjBean.getFSZT());
            pstmt.setInt(7, qhsssjBean.getYXH());
            pstmt.setString(8, qhsssjBean.getCJSJ());
            pstmt.setInt(9, qhsssjBean.getCC());
            pstmt.setDouble(10, qhsssjBean.getXSZL());
            pstmt.setInt(11, qhsssjBean.getSMYC());
            pstmt.setInt(12, qhsssjBean.getJLYC());
            pstmt.setInt(13, qhsssjBean.getWKYC());
            pstmt.setInt(14, qhsssjBean.getTXYC());
            pstmt.setInt(15, qhsssjBean.getFSZT());
            pstmt.setString(16, qhsssjBean.getYCBS());
            
            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }
    
    //准备处理前n项记录，预设前n项记录uploadStatus='1'
    public boolean prepareUpload(int n) throws Exception{
         boolean flag = false;            
            PreparedStatement pstmt = null;
            String sql = "UPDATE QHSSSJ SET [uploadStatus]='1' from (select top "+n+" id from QHSSSJ where uploadStatus='0' order by CJSJ) as b where QHSSSJ.id = b.id  ";
            try {
                    pstmt = this.conn.prepareStatement(sql);
                    int len = pstmt.executeUpdate();
                    if (len > 0) {
                            flag = true;
                    }
            } catch (Exception e) {
                    throw e;
            } finally {
                    try {
                            pstmt.close();
                    } catch (Exception e) {
                            throw e;
                    }
            }
            return flag;
    }
    
    //完成传送，设置uploadStatus=2
    public boolean finishUpload() throws Exception{
         boolean flag = false;            
            PreparedStatement pstmt = null;
            String sql = "UPDATE QHSSSJ SET [uploadStatus]=2 where uploadStatus=1 ";
            try {
                    pstmt = this.conn.prepareStatement(sql);
                    int len = pstmt.executeUpdate();
                    if (len > 0) {
                            flag = true;
                    }
            } catch (Exception e) {
                    throw e;
            } finally {
                    try {
                            pstmt.close();
                    } catch (Exception e) {
                            throw e;
                    }
            }
            return flag;
    }
    
    //获取前几项未传送信息
    public List<QHSSSJBean> getTops(int n) throws Exception {
        List<QHSSSJBean> all = new ArrayList<QHSSSJBean>();
        PreparedStatement pstmt = null;
	String sql = "select top "+n+" id,MKBH,SHI,XIAN,MKMC,SBBH,TSFS,YXH,CJSJ,CC,XSZL,SMYC,JLYC,WKYC,TXYC,FSZT,YCBS,uploadStatus from QHSSSJ  where uploadStatus=1 order by CJSJ asc;";
        try {
            pstmt = this.conn.prepareStatement(sql);
            //pstmt.setInt(1, n);            
            ResultSet rs = pstmt.executeQuery(); //
            long id;    
            String MKBH;
            String SHI; 
            String XIAN;
            String MKMC;
            String SBBH;
            int TSFS;   
            int YXH;    
            String CJSJ;
            int CC;     
            double XSZL;
            int SMYC;   
            int JLYC;   
            int WKYC;   
            int TXYC;   
            int FSZT;   
            String YCBS;
            String uploadStatus; 

            while(rs.next()){
                id = rs.getLong(1);  
                MKBH=rs.getString(2);
                SHI=rs.getString(3); 
                XIAN=rs.getString(4);
                MKMC=rs.getString(5);
                SBBH=rs.getString(6);
                TSFS=rs.getInt(7);
                YXH=rs.getInt(8); 
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd/HH:mm");
                Date cjsj = rs.getTimestamp(9);
                CJSJ=sdf.format(cjsj);
                logger.info(CJSJ);
                CC=rs.getInt(10);  
                XSZL=rs.getDouble(11);
                SMYC=rs.getInt(12);
                JLYC=rs.getInt(13);
                WKYC=rs.getInt(14);
                TXYC=rs.getInt(15);
                FSZT=rs.getInt(16);
                YCBS=rs.getString(17);
                uploadStatus=rs.getString(18); 
                QHSSSJBean qhssj= new QHSSSJBean();
                qhssj.setCC(CC);
                qhssj.setCJSJ(CJSJ);
                qhssj.setId(id);
                qhssj.setMKBH(MKBH);
                qhssj.setSHI(SHI);
                qhssj.setXIAN(XIAN);
                qhssj.setMKMC(MKMC);
                qhssj.setSBBH(SBBH);
                qhssj.setTSFS(TSFS);
                qhssj.setYXH(YXH);
                qhssj.setCJSJ(CJSJ);
                qhssj.setCC(CC);
                qhssj.setXSZL(XSZL);
                qhssj.setSMYC(SMYC);
                qhssj.setJLYC(JLYC);
                qhssj.setWKYC(WKYC);
                qhssj.setTXYC(TXYC);
                qhssj.setFSZT(FSZT);
                qhssj.setYCBS(YCBS);
                qhssj.setUploadStatus(uploadStatus);
                all.add(qhssj);
            }
        } catch (Exception e) {
                e.printStackTrace();    
                throw e;
                
        } finally {
                try {
                        pstmt.close();
                } catch (Exception e) {
                  throw e;      
                }
        }        
        return all;
    }
    
    public boolean changeStatusById(long id,int status) throws Exception {
        boolean flag = false;
            
            PreparedStatement pstmt = null;
            String sql = "UPDATE QHSSSJ SET [uploadStatus]=? WHERE id=?";
            try {
                    pstmt = this.conn.prepareStatement(sql);
                    pstmt.setString(1,new Integer(status).toString());
                    pstmt.setLong(2, id);
                    int len = pstmt.executeUpdate();
                    if (len > 0) {
                            flag = true;
                    }
            } catch (Exception e) {
                    throw e;
            } finally {
                    try {
                            pstmt.close();
                    } catch (Exception e) {
                            throw e;
                    }
            }
            return flag;
    }
}
